USE [NguyenHoa]
GO
-------------------------------------------------------------------------
--						@tblProduct
-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblProductLocale_GetByCatLang]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblProductLocale_GetByCatLang]
GO

CREATE PROCEDURE [dbo].[_tblProductLocale_GetByCatLang]
(
	@LangId int
)
AS
	SELECT 
		Pl.[Id],
		Pl.[ProductId],
		Pl.[Name],
		Pl.[Description],
		Pl.[RefPrice],
		Pl.[Content],
		P.[Code], P.[AlbumId]
	FROM	tblProduct P INNER JOIN tblProductLocale Pl ON P.[Id]=Pl.[ProductId]
	WHERE	Pl.[LanguageId]=@LangId
	ORDER BY P.[CreatedDate] DESC
GO

-------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblProduct_DeleteAllById]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblProduct_DeleteAllById]
GO

CREATE PROCEDURE [dbo].[_tblProduct_DeleteAllById]
(
	@Id int
)
AS
	BEGIN TRAN
		DELETE tblProductLocale WHERE [ProductId]=@Id
		IF @@ERROR <> 0
			ROLLBACK TRAN
		DELETE tblProduct WHERE [Id]=@Id
		IF @@ERROR <> 0
			ROLLBACK TRAN
		COMMIT TRAN
GO
-------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblProduct_GetOtherProducts]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblProduct_GetOtherProducts]
GO

CREATE PROCEDURE [dbo].[_tblProduct_GetOtherProducts]
(
	@Id int,
	@Count int
)
AS
	IF(@Count IS NULL OR @Count=0)
		BEGIN
			SELECT P.* FROM tblproduct P
			WHERE p.[Id]<>@Id
			ORDER BY P.[CreatedDate] DESC
		END
	ELSE
		BEGIN
			DECLARE @SQL varchar(2048)
			SET @SQL='SELECT TOP '+CAST(@Count AS VARCHAR(10))+' P.* FROM tblproduct P
						WHERE p.[Id]<>'+CAST(@Id AS VARCHAR(10))+' ORDER BY P.[CreatedDate] DESC'
			EXEC(@SQL)
		END 
	
GO
-------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id=object_id(N'[dbo].[_tblProductLocale_GetOtherProducts]') and type IN (N'PC', N'P'))
DROP PROCEDURE [dbo].[_tblProductLocale_GetOtherProducts]
GO

CREATE PROCEDURE [dbo].[_tblProductLocale_GetOtherProducts]
(
	@Id int,
	@Count int
)
AS
	IF(@Count IS NULL OR @Count=0)
		BEGIN
			SELECT P.*
			FROM tblproductLocale P INNER JOIN tblProduct PR ON P.[ProductId]=PR.[Id]
			WHERE p.[ProductId]<>@Id
			ORDER BY PR.[CreatedDate] DESC
		END
	ELSE
		BEGIN
			DECLARE @SQL varchar(2048)
			SET @SQL='SELECT TOP '+CAST(@Count AS VARCHAR(10))+' P.*,PR.CategoryId 
						FROM tblProductLocale P INNER JOIN tblProduct PR ON P.[ProductId]=PR.[Id]
						WHERE p.[Id]<>'+CAST(@Id AS VARCHAR(10))+' ORDER BY PR.[CreatedDate] DESC'
			EXEC(@SQL)
		END 
	
GO
-------------------------------------------------------------------------